﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using eTerm.MsSql.DbProvider;
using NPOI.SS.UserModel;
using System.Text.RegularExpressions;
using NPOI.HSSF.Util;
using eTerm.Platform.Common;

namespace eTerm.Platform.Business
{

    /// <summary>
    /// 通用组件NPOI读取Excel导入票号
    /// </summary>
    public class NPOIDetr
    {
        HSSFWorkbook hssfworkbook;
        private int? UserID = null;
        public NPOIDetr(string excelFileName) {
            InitializeWorkbook(excelFileName);
        }

        public NPOIDetr() { 
            
        }


        public NPOIDetr(Stream fileStream) {
            hssfworkbook = new HSSFWorkbook(fileStream);
        }


        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }


        private string GetTicketDate(IRow row) {
            string ticketDate = string.Empty;
            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null && Regex.IsMatch(cell.ToString(), @"\d{4}[\-\/]\d{1,2}[\-\/]\d{1,2}", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                {
                    ticketDate = Regex.Match(cell.ToString(), @"\d{4}[\-\/]\d{1,2}[\-\/]\d{1,2}", RegexOptions.IgnoreCase | RegexOptions.Multiline).Value;
                    break;
                }

            }
            return ticketDate;
        }


        private string GetAirWayCode(IRow row) {
            string airWayCode = string.Empty;

            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null && Regex.IsMatch(cell.ToString(), @"^(\d{3})$", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                {
                    airWayCode = Regex.Match(cell.ToString(), @"^(\d{3})$", RegexOptions.IgnoreCase | RegexOptions.Multiline).Groups[1].Value;

                    break;
                }


                if (cell != null && Regex.IsMatch(cell.ToString(), @"^(\d{3})(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                {
                    airWayCode = Regex.Match(cell.ToString(), @"^(\d{3})(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline).Groups[1].Value;
                    break;
                }

            }
            return airWayCode;
        }

        private string GetTicketNo(IRow row) {
            string ticketNo = string.Empty;

            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null && Regex.IsMatch(cell.ToString(), @"^(\d{3})\-(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                {
                    ticketNo = Regex.Match(cell.ToString(), @"^(\d{3})\-(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline).Value;
                    break;
                }

                if (cell != null && Regex.IsMatch(cell.ToString(), @"^(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                {
                    ticketNo = Regex.Match(cell.ToString(), @"^(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline).Groups[1].Value;
                    break;
                }

                if (cell != null && Regex.IsMatch(cell.ToString(), @"^(\d{3})(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline)) {
                    ticketNo = Regex.Match(cell.ToString(), @"^(\d{3})(\d{10})$", RegexOptions.IgnoreCase | RegexOptions.Multiline).Groups[2].Value;
                    break;
                }

            }
            return ticketNo;
        }

        List<PnrDetr> ReadExcel(ISheet sheet) {
            List<PnrDetr> items = new List<PnrDetr>();
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            while (rows.MoveNext()) {
                IRow row = (HSSFRow)rows.Current;
                string ticketNo = GetTicketNo(row);
                string ticketDate = GetTicketDate(row) ;
                string airWayCode = GetAirWayCode(row);
                if (!Regex.IsMatch(ticketNo, @"\-", RegexOptions.IgnoreCase | RegexOptions.Multiline))
                    ticketNo = string.Format(@"{0}-{1}", airWayCode, ticketNo);

                    if(ticketNo.Length==14)
                        items.Add(new PnrDetr() { 
                         StatusId = (int)DetrStatus.PENDDING,
                          TicketDate = string.IsNullOrEmpty(ticketDate)?null:DateTime.Parse(ticketDate) as DateTime?,
                           TicketNo = ticketNo,
                            ProcessDate = DateTime.Now,
                             UserId= UserID
                        });

            }
            return items;
        }


        public List<PnrDetr> ReadExcel(int? UID) {
            this.UserID = UID;
            List<PnrDetr> items = new List<PnrDetr>();
            for(int i=0;i<hssfworkbook.NumberOfSheets;i++)
                items.AddRange(ReadExcel(hssfworkbook.GetSheetAt(i)));
            return items.Distinct(new DetrIdentifyComparer()).ToList();
        }


        public Stream ToExcel(string CompanyCode,int? UID,DateTime? StartDate,DateTime? EndDate,int StatusID)
        {
            var sql = PnrDetr.All().Where(l=>(l.StatusId & StatusID)!=0)
                .Join(SysUser.All(), l => l.UserId, r => r.UserId, (l, r) => new { r.CompanyCode, r.DisplayName, r.Email, l.StatusId, l.TicketNo, l.TicketDate, l.Id, l.ProcessDate, r.UserId })
                .Join(SysCompany.All(), l => l.CompanyCode, r => r.CompanyCode, (l, r) => new { l.CompanyCode, l.DisplayName, l.Email, l.Id, l.StatusId, l.TicketDate, l.TicketNo, l.ProcessDate, l.UserId, r.CompayName })
                .SelectMany(l => PnrSegment.All().Where(g=>(g.StatusId & StatusID) !=0).GroupBy(g => g.TicketNoId, (Key, Items) => new { Key, FlightNos = Items.Select(y =>string.Format("{0}({1}-{2})", y.FlightNo,y.Departure,y.Arrival)) }).Where(n => n.Key == l.Id).DefaultIfEmpty(), (l, r) => new { l.CompanyCode, l.CompayName, l.DisplayName, l.Email, l.Id, l.ProcessDate, l.StatusId, l.TicketDate, l.TicketNo, l.UserId, r.FlightNos })
                //.ToList()
                ;

            if (!string.IsNullOrEmpty(CompanyCode))
                sql = sql.Where(l => l.CompanyCode.Contains(CompanyCode));

            if (UID.HasValue)
                sql = sql.Where(l => l.UserId == UID.Value);

            if (StartDate.HasValue)
                sql = sql.Where(l => l.ProcessDate >= StartDate.Value);

            if (EndDate.HasValue)
                sql = sql.Where(l => l.ProcessDate <= EndDate.Value.AddDays(1));

            

            int rowIndex = 0;

            //创建workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            ISheet sheet = workbook.CreateSheet("票号清单");
            IRow row = sheet.CreateRow(rowIndex);


            //表头样式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;//居中对齐
            //表头单元格背景色
            style.FillForegroundColor = HSSFColor.SKY_BLUE.index;
            style.FillPattern = FillPatternType.SOLID_FOREGROUND;
            //表头单元格边框
            style.BorderTop = BorderStyle.DASH_DOT;
            style.TopBorderColor = HSSFColor.BLACK.index;
            style.BorderRight = BorderStyle.DASH_DOT;
            style.RightBorderColor = HSSFColor.BLACK.index;
            style.BorderBottom = BorderStyle.DASH_DOT;
            style.BottomBorderColor = HSSFColor.BLACK.index;
            style.BorderLeft = BorderStyle.DASH_DOT;
            style.LeftBorderColor = HSSFColor.BLACK.index;
            //表头字体设置
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = 600;//加粗
            font.Color = HSSFColor.WHITE.index;//颜色
            style.SetFont(font);

            //数据样式
            ICellStyle datastyle = workbook.CreateCellStyle();
            datastyle.Alignment = HorizontalAlignment.CENTER;//左对齐
            //数据单元格的边框
            datastyle.BorderTop = BorderStyle.DASH_DOT;
            datastyle.TopBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderRight = BorderStyle.DASH_DOT;
            datastyle.RightBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderBottom = BorderStyle.DASH_DOT;
            datastyle.BottomBorderColor = HSSFColor.BLACK.index;
            datastyle.BorderLeft = BorderStyle.DASH_DOT;
            datastyle.LeftBorderColor = HSSFColor.BLACK.index;
            //数据的字体
            IFont datafont = workbook.CreateFont();
            datafont.FontHeightInPoints = 11;//字号
            datastyle.SetFont(datafont);

            //设置列宽
            sheet.SetColumnWidth(0, 20 * 256);
            int colWidth0 = sheet.GetColumnWidth(0);

            sheet.SetColumnWidth(1, 60 * 256);
            int colWidth1 = sheet.GetColumnWidth(1);

            sheet.SetColumnWidth(2, 20 * 256);
            int colWidth2 = sheet.GetColumnWidth(2);

            sheet.SetColumnWidth(3, 35 * 256);
            int colWidth3 = sheet.GetColumnWidth(3);

            sheet.SetColumnWidth(4, 20 * 256);
            int colWidth4 = sheet.GetColumnWidth(4);

            sheet.SetColumnWidth(5, 60 * 256);
            int colWidth5 = sheet.GetColumnWidth(5);

            //设置行高
            sheet.DefaultRowHeight = 200 * 20;

            //表头数据
            ICell cell0 = row.CreateCell(0);
            cell0.SetCellValue("票号");
            cell0.CellStyle = style;

            ICell cell1 = row.CreateCell(1);
            cell1.SetCellValue("出票用户");
            cell1.CellStyle = style;

            ICell cell2 = row.CreateCell(2);
            cell2.SetCellValue("出票日期");
            cell2.CellStyle = style;

            ICell cell3 = row.CreateCell(3);
            cell3.SetCellValue("状态");
            cell3.CellStyle = style;

            ICell cell4 = row.CreateCell(4);
            cell4.SetCellValue("导入日期");
            cell4.CellStyle = style;

            ICell cell5 = row.CreateCell(5);
            cell5.SetCellValue("航班列表");
            cell5.CellStyle = style;

            //填充数据
            foreach (var fd in sql.ToList())
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                cell0 = row.CreateCell(0);
                cell0.SetCellValue(fd.TicketNo);
                cell0.CellStyle = datastyle;

                cell1 = row.CreateCell(1);
                cell1.SetCellValue(string.Format(@"{0}({1})",fd.DisplayName,fd.CompayName));
                cell1.CellStyle = datastyle;
                //cell1.CellStyle.Alignment = HorizontalAlignment.LEFT;

                cell2 = row.CreateCell(2);
                cell2.SetCellValue(fd.TicketDate.HasValue?fd.TicketDate.Value.ToString(@"yyyy-MM-dd"):string.Empty);
                cell2.CellStyle = datastyle;

                cell3 = row.CreateCell(3);
                cell3.SetCellValue(EnumHelper.GetEnumName((DetrStatus)fd.StatusId));
                cell3.CellStyle = datastyle;

                //cell3.CellStyle = dateStyle;

                cell4 = row.CreateCell(4);
                cell4.SetCellValue(fd.ProcessDate.ToString(@"yyyy-MM-dd HH:mm"));
                cell4.CellStyle = datastyle;

                cell5 = row.CreateCell(5);
                cell5.SetCellValue(fd.FlightNos==null?string.Empty: @"["+string.Join("][", fd.FlightNos.Distinct().ToArray())+@"]");
                cell5.CellStyle = datastyle;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            workbook = null;
            sheet = null;
            row = null;

            return ms;
        }

    }


    public class DetrIdentifyComparer : IEqualityComparer<PnrDetr>
    {
        // SellerType are equal if their names and product numbers are equal. 
        public bool Equals(PnrDetr x, PnrDetr y)
        {
            //Check whether the compared objects reference the same data. 
            if (Object.ReferenceEquals(x, y)) return true;

            if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
                return false;

            return x.TicketNo == y.TicketNo;
        }
        public int GetHashCode(PnrDetr product)
        {
            //Check whether the object is null  
            if (Object.ReferenceEquals(product, null)) return 0;

            //Get hash code for the SellerTypeID field if it is not null.  
            int SellerTypeId = product.TicketNo == null ? 0 : product.TicketNo.GetHashCode();

            //Calculate the hash code for the SellerType.  
            return SellerTypeId;
        }
    } 
}
